package org.wwy.demo.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;

import static org.apache.poi.ss.usermodel.ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE;

public class ExcelImageTest {
    public static void main(String[] args) {  
         FileOutputStream fileOut = null;
         BufferedImage bufferImg = null;
        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray    
        try {  
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            bufferImg = ImageIO.read(new File("d:/test.png"));
            ImageIO.write(bufferImg, "png", byteArrayOut);

            // 拿到模板文件
            String filePath = "d:/mytmp.xls";
            FileInputStream tps = new FileInputStream(new File(filePath));
            final HSSFWorkbook tpWorkbook = new HSSFWorkbook(tps);

            HSSFWorkbook wb = tpWorkbook;
            HSSFSheet sheet = wb.getSheetAt(0);
            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）  
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            //anchor主要用于设置图片的属性  
//            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255,(short) 1, 1, (short) 5, 8);
            HSSFClientAnchor anchor = new HSSFClientAnchor();
            anchor.setCol1(2);
            anchor.setRow1(3);
            anchor.setCol2(10);
            anchor.setRow2(20);
            anchor.setAnchorType(DONT_MOVE_AND_RESIZE);
            //插入图片    
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

            int startrow = 24;
            int startcol = 1;
            for(int i=0;i<10;i++){
                // 在这个sheet页创建一行，从0开始
                Row row = sheet.createRow((short) i+startrow);
                for(int j=0;j<15;j++){
                    // 在这一行创建一个单元格，在0号位
                    Cell cell = row.createCell(j+startcol);
                    // 给这个单元格赋值为1
                    cell.setCellValue(i+"-"+j+"-content");
                }

            }


            fileOut = new FileOutputStream("D:/测试Excel.xls");     
            // 写入excel文件     
             wb.write(fileOut);
             System.out.println("----Excle文件已生成------");  
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(fileOut != null){  
                 try {  
                    fileOut.close();  
                } catch (IOException e) {
                    e.printStackTrace();  
                }  
            }  
        }  
    }  
}  
